﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.IO;

namespace RouterControl.LocalDb
{
    public sealed partial class LocalDbConnector : IDisposable
    {
        public const int LocalDbVersion = 2;
        private static readonly Guid GlobalMutexGuid = new Guid("{92CCBE3D-5D8E-46f1-A26E-C4460A623488}");

        private readonly log4net.ILog _log = log4net.LogManager.GetLogger(typeof(LocalDbConnector));
        private SQLiteConnection _db;
        private readonly FileInfo _dbFile;

        public int Version
        {
            get { return int.Parse(GetProperty("LocalDbVersion") ?? "0"); }
            private set { SetProperty("LocalDbVersion", value.ToString()); }
        }

        /// <summary>
        /// for testing only
        /// </summary>
        internal LocalDbConnector(FileInfo localDbFile)
        {
            if (localDbFile == null)
	            throw new ArgumentNullException("localDbFile");

            _dbFile = localDbFile;

            if (_log.IsDebugEnabled)
                _log.Debug("Local database path: " + _dbFile.FullName);

            // ensure the file exists; use
            // template if not
            if (!_dbFile.Exists)
            {
                using (new Whs.GlobalMutex(GlobalMutexGuid).ForceLock)
                {
                    _dbFile.Refresh();

                    if (!_dbFile.Exists)
                    {
                        _log.Info("Local database does not exist; creating from template");

                        File.Copy(
                            Properties.Settings.Default.TemplateDbPath,
                            _dbFile.FullName,
                            false
                            );

                        _dbFile.Refresh();
                    }
                }
            }

            // connect database
            var csb = new SQLiteConnectionStringBuilder();

            csb.DataSource = _dbFile.FullName;
            csb.FailIfMissing = true;
            
            _db = new SQLiteConnection(csb.ToString());

            _db.Open();

            // upgrade database file, if required
            if (this.Version < LocalDbVersion)
            {
                using (new Whs.GlobalMutex(GlobalMutexGuid).ForceLock)
                {
                    if (this.Version < LocalDbVersion)
                    {
                        UpgradeDatabase();

                        MaintainDatabase();
                    }
                }
            }
        }

        public LocalDbConnector()
            : this(new FileInfo(Properties.Settings.Default.LocalDbPath))
        {
        }

        /// <summary>
        /// deletes the local database file
        /// 
        /// use for emergency cases where the database file
        /// is broken
        /// </summary>
        public static void DeleteDatabase()
        {
            var dbFile = new FileInfo(Properties.Settings.Default.LocalDbPath);

            if (!dbFile.Exists)
                return;

            File.Replace(
                Properties.Settings.Default.TemplateDbPath,
                dbFile.FullName,
                dbFile.FullName + "." + DateTime.Now.ToString("yyyyMMdd-HHmmss")
                );
        }

        internal string GetProperty(string key)
        {
            if (string.IsNullOrEmpty(key))
                throw new ArgumentException("null or empty", "key");

            try
            {
	            var cmd = _db.CreateCommand();
	
	            cmd.CommandText = "select Value from Properties where Key=@k limit 1";
	            cmd.Parameters.AddWithValue("@k", key);
	
	            return Convert.ToString(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                _log.Error("Failed to get property: " + key, ex);

                throw;
            }
        }

        internal void SetProperty(string key, string value)
        {
            if (string.IsNullOrEmpty(key))
                throw new ArgumentException("null or empty", "key");

            try
            {
	            var cmd = _db.CreateCommand();
	
	            cmd.CommandText = "insert or replace into Properties (Key,Value) values (@k,@v)";
	            cmd.Parameters.AddWithValue("@k", key);
	            cmd.Parameters.AddWithValue("@v", value);
	
	            cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                _log.Error("Failed to set property: " + key + "=" + value, ex);

                throw;
            }
        }

        public void MaintainDatabase()
        {
            try
            {
                if (_log.IsDebugEnabled)
                    _log.Debug("Maintaining local database...");

                // continue
                var cmd = _db.CreateCommand();

                cmd.CommandText = "vacuum; analyze";

                cmd.ExecuteNonQuery();

                // finalize
                _log.Info("Local database maintained");
            }
            catch (Exception ex)
            {
                _log.Error("Failed to maintain database", ex);

                throw;
            }
        }

        /// <summary>
        /// checks the integrity of the database
        /// 
        /// returns a list of errors
        /// </summary>
        public IList<string> CheckIntegrity()
        {
            try
            {
                _log.Info("Checking local database...");

                // continue
                var cmd = _db.CreateCommand();

                cmd.CommandText = "pragma integrity_check";

                // read errors
                var ret = new List<string>();

                using (var r = cmd.ExecuteReader())
                {
                    while (r.Read())
                    {
                        var errMsg = r.GetString(0);

                        if (errMsg == "ok")
                            continue;

                        // handle error
                        _log.Warn("Local database integrity failure: " + errMsg);

                        ret.Add(errMsg);
                    }
                }

                // finalize
                _log.Info("Local database checked");

                return ret.AsReadOnly();
            }
            catch (Exception ex)
            {
                _log.Error("Failed to check database", ex);

                throw;
            }
        }

        internal bool HasObject(string dbObjectName)
        {
            if (string.IsNullOrEmpty(dbObjectName))
                throw new ArgumentException("null or empty", "tableName");

            try
            {
                var cmd = _db.CreateCommand();

                cmd.CommandText = "select 1 from sqlite_master where name like @t limit 1";
                cmd.Parameters.AddWithValue("@t", dbObjectName);

                return Convert.ToBoolean(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                _log.Error("Failed to check for database object: " + dbObjectName, ex);

                throw;
            }
        }

        public long GetDatabaseFileSize()
        {
            _dbFile.Refresh();

            return _dbFile.Length;
        }

        public float GetUnusedPagesPercentage()
        {
            try
            {
                int total;
                int unused;

                // continue
                {
                    var cmd = _db.CreateCommand();

                    cmd.CommandText = "pragma page_count";

                    total = Convert.ToInt32(cmd.ExecuteScalar());
                }

                {
                    var cmd = _db.CreateCommand();

                    cmd.CommandText = "pragma freelist_count";

                    unused = Convert.ToInt32(cmd.ExecuteScalar());
                }

                // finalize
                if (total <= 0)
                    return 0.0f;

                return ((float)unused / total);
            }
            catch (Exception ex)
            {
                _log.Error("Failed to retrieve unsued pages percentage", ex);

                throw;
            }
        }

        public void Dispose()
        {
            if (_db != null)
                _db.Dispose();
        }

    }
}
